🔍 Data briefing

The spreadsheet elucidates the procedural intricacies of the existing decision model employed in demand forecasting, encompassing ABC-XYZ analysis and the establishment of safety stock parameters with the support of three different category of pseudo example.

📈 Historical pattern

Upon Figure 1 analysis of the 12-month historical demand data(Square-root transformation to reduce heteroscedasticity) , discernible patterns emerge that we may notice:

Example 1: A bimodal distribution with two prominent peaks(Jun and Nov)

Example 2: a relative flatter line with less fluctuation by seasonality, with gradual decline since 2023 Jun.

Example 3: Seasonal product, no demand in warm weather

Figure 1: The last 12 month of Square Root Transformed [move-out quantity]

💡 demand forecasting

🎯 Model introduction

There have two common models in forecasting scope:

  1. The ETS model is a time series forecasting method used to model and predict data patterns by capturing the quantified index of three components: error (random fluctuations), trend (long-term movement), and seasonality (repeating patterns at fixed intervals).

  2. ARIMA model (AutoRegressive Integrated Moving Average) is a time series forecasting model that combines autoregressive (AR,the number of lag observations) and moving average (MA, the number of lagged forecast errors ) components along with differencing(\(d\),the number of times differencing) to handle non-stationary data. ARIMA is widely used for analyzing and predicting time series data by capturing patterns, trends, and seasonality in the temporal sequence. The formula is:

\[Y_t =μ+ϕ_1Y_{t−1} +ϕ_2Y_{t−2} +... ϕ_pY_t−p +θ_1ϵ_{t-1}+θ_2ϵ_{t-2}+...+ϵ_t\] 3. Difference: It is a commonly held myth that ARIMA models are more general than exponential smoothing.While linear exponential smoothing models are all special cases of ARIMA models, the non-linear exponential smoothing models have no equivalent ARIMA counterparts. On the other hand, there are also many ARIMA models that have no exponential smoothing counterparts. In particular, all ETS models are non-stationary, while some ARIMA models are stationary. More reference on fpp textbook

🎯Example studying for product 1

ETS model decomposition

The breakdown ETS model Figure 2 indicate it failed to capture the seasonality and the slope of repetitive trend. In turn, it became a piecewise linear model structured by 1)level:the baseline or average value of the time series, and 2）remainder: the white noise or residual for the model fitting, which persisting at high levels.

The breakdown of ETS analysis

Figure 2: The breakdown of ETS analysis

ARIMA model decomposition

The arima model demonstrates a marginal improvement compared to Model B in terms of performance but the prediction still fall into the logarithm distribution since the insufficient lag and high degree of freedom provided.

🧭 ABC-XYZ Analysis

In the structure of the ABC-XYZ Analysis, it is noteworthy that the ABC category, delineating the value and revenue contribution, is anticipated to exhibit stability. Meanwhile, prudent consideration should be given to the XYZ category, taking into account the principles of nil demand months and coefficient variability:

X articles have demand coefficent variability < 0.5 Y articles have demand coefficent variability between 0.5 and 1.0 Z articles have demand coefficient variabilty > 1.0 Z2 articles are those Z articles with 6 months or more with zero demand history

Below Table 1 shows the result of XYZ identification for example 1 forecasting:
Table 1: Table 2: The XYZ prediction for Example 1 forecast
date quantity rollmean rollsd rollCV zero month XYZ
2024 Mar 18566.51 29640.59 10931.519 0.37 0 X
2024 Apr 20279.88 26231.07 9922.035 0.38 0 X
2024 May 21441.36 23116.46 7263.855 0.31 0 X
2024 Jun 22228.72 20774.25 3416.396 0.16 0 X
2024 Jul 22762.46 20219.66 2538.076 0.13 0 X
2024 Aug 23124.27 21400.53 1720.536 0.08 0 X

🌏 Safety stock

Once the XYZ category is identified, we can proceed to the next phase to establish safety stock. Here are the key principles:

1.Statistical safety stocks can only be used reliably where the demand volatility is low (ie X or Y articles). Statistical safety stocks should not be used for Z / Z2 articles.

2.A target of 95% is usually adequate for finished goods. The correlation of target service level and safety factor can be explained as:

  1. For own manufactured product, the lead time will be 10 days, for merchandiseoutsourcing, it usually took 45 days.

Let’s assume the target of example 1 will maintain 95%. Thus, the safety stock correlation shall be 1.65.

Below Table 1 shows the result of statistical safety stock for example 1 forecasting:
Table 3: Table 4: The statistical safety stock for Example 1 forecast
description replenishment_lt safety factor demand_lt safety stock
Example_1 10 1.65 9880.195 10461.46
Example_1 10 1.65 8743.689 9495.39
Example_1 10 1.65 7705.486 6951.51
Example_1 10 1.65 6924.749 3269.49
Example_1 10 1.65 6739.885 2428.94
Example_1 10 1.65 7133.511 1646.55

📜 Following up

  1. More visibility in sales data : extended timeframe(3-5 years?) regional breakdown?

  2. Missing information of other division: dimension information — volumetric weight vs actual weight? network capacity?

  3. The understanding of abbreviation in spreadsheet

  4. Convert the data analysis to the interactive shinyapp Example



Version 1.0 | This webpage is licensed by CC 3.0 | Monash AMES project team